﻿using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data.OleDb;

namespace DAO
{
    public class HoaDonDAO
    {
        //
        //Select HoaDon
        //
        public List<DTO.HoaDonDTO> SelectAll_returnList()
        {
            List<DTO.HoaDonDTO> lst = new List<HoaDonDTO>();
            OleDbConnection conn = DataProvider.ConnectDB();
            conn.Open();
            OleDbCommand cmd = new OleDbCommand("select * from HoaDon", conn);
            OleDbDataReader rd = cmd.ExecuteReader();
            while (rd.Read())
            {
                DTO.HoaDonDTO hdDTO = new HoaDonDTO();
                hdDTO.MaHD = rd.GetInt32(0);
                hdDTO.NgayHD = rd.GetDateTime(1);
                hdDTO.SoHD = rd.GetString(2);
                hdDTO.MaKH = rd.GetInt32(3);
                hdDTO.TenKH = rd.GetString(4);
                hdDTO.ThanhTien = rd.GetDouble(5);
                hdDTO.MaNV = rd.GetInt32(6);
                hdDTO.LoaiKH = rd.GetBoolean(7);
                hdDTO.TenNV = rd.GetString(8);
                lst.Add(hdDTO);
            }
            conn.Close();
            return lst;
        }
        public System.Data.DataSet SelectAll_returnDataSet(DateTime batdau, DateTime ketthuc)
        {
            System.Data.DataSet ds = new System.Data.DataSet();
            OleDbConnection conn = DataProvider.ConnectDB();
            string sql = "select *,IIf(hd.TinhTrang=Yes,'Đã xuất','Chưa xuất') as TT, (select nv.HoTen from NhanVien nv where nv.MaNV=hd.MaNV) as TenNV  from HoaDon as hd where hd.NgayHD between " + "#" + batdau.ToShortDateString() + "#" + " and " + "#" + ketthuc.ToShortDateString() + "#";
            OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);
            int kq = da.Fill(ds, "HoaDon");
           // da.Dispose();
            conn.Close();
            return ds;
        }
        public List<DTO.HoaDonDTO> LayHoaDonTheoNgay(DateTime batdau, DateTime ketthuc)
        {
            List<DTO.HoaDonDTO> lst = new List<HoaDonDTO>();
            OleDbConnection conn = DataProvider.ConnectDB();
            conn.Open();
            string sql = "select * from HoaDon where NgayHD between " + "#" + batdau.ToShortDateString() + "#" + " and " + "#" + ketthuc.ToShortDateString() + "# ";
            // string sql = "select * from HoaDon where NgayHD between #5/11/2013# and #5/11/2013#";
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader rd = cmd.ExecuteReader();
            while (rd.Read())
            {
                DTO.HoaDonDTO hdDTO = new HoaDonDTO();
                hdDTO.MaHD = rd.GetInt32(0);
                hdDTO.NgayHD = rd.GetDateTime(1);
                hdDTO.SoHD = rd.GetString(2);
                hdDTO.MaKH = rd.GetInt32(3);
                hdDTO.TenKH = rd.GetString(4);
                hdDTO.ThanhTien = rd.GetDouble(5);
                hdDTO.MaNV = rd.GetInt32(6);
                hdDTO.LoaiKH = rd.GetBoolean(7);

                lst.Add(hdDTO);
            }
            conn.Close();
            return lst;
        }
        public List<DTO.HoaDonDTO> LayHoaDonNVTheoNgay(DateTime batdau, DateTime ketthuc)
        {
            List<DTO.HoaDonDTO> lst = new List<HoaDonDTO>();
            OleDbConnection conn = DataProvider.ConnectDB();
            string sql = "select hd.MaHD,hd.NgayHD,hd.SoHD,hd.MaKH,hd.TenKH,hd.ThanhTien,hd.MaNV,hd.LoaiKH, (select nv.HoTen from NhanVien nv where nv.MaNV=hd.MaNV) as TenNV  from HoaDon as hd where hd.NgayHD between " + "#" + batdau.ToShortDateString() + "#" + " and " + "#" + ketthuc.ToShortDateString() + "# and hd.LoaiKH=true";
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader rd = cmd.ExecuteReader();
            while (rd.Read())
            {
                DTO.HoaDonDTO hdDTO = new HoaDonDTO();
                hdDTO.MaHD = rd.GetInt32(0);
                hdDTO.NgayHD = rd.GetDateTime(1);
                hdDTO.SoHD = rd.GetString(2);
                hdDTO.MaKH = rd.GetInt32(3);
                hdDTO.TenKH = rd.GetString(4);
                hdDTO.ThanhTien = rd.GetDouble(5);
                hdDTO.MaNV = rd.GetInt32(6);
                hdDTO.LoaiKH = rd.GetBoolean(7);
                hdDTO.TenNV = rd.GetString(8);
                lst.Add(hdDTO);
            }
            conn.Close();
            return lst;
        }
        public List<DTO.HoaDonDTO> LayHoaDonKLTheoNgay(DateTime batdau, DateTime ketthuc)
        {
            List<DTO.HoaDonDTO> lst = new List<HoaDonDTO>();
            OleDbConnection conn = DataProvider.ConnectDB();
            string sql = "select hd.MaHD,hd.NgayHD,hd.SoHD,hd.MaKH,hd.TenKH,hd.ThanhTien,hd.MaNV,hd.LoaiKH, (select nv.HoTen from NhanVien nv where nv.MaNV=hd.MaNV) as TenNV  from HoaDon as hd where hd.NgayHD between " + "#" + batdau.ToShortDateString() + "#" + " and " + "#" + ketthuc.ToShortDateString() + "# and hd.LoaiKH=false";
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader rd = cmd.ExecuteReader();
            while (rd.Read())
            {
                DTO.HoaDonDTO hdDTO = new HoaDonDTO();
                hdDTO.MaHD = rd.GetInt32(0);
                hdDTO.NgayHD = rd.GetDateTime(1);
                hdDTO.SoHD = rd.GetString(2);
                hdDTO.MaKH = rd.GetInt32(3);
                hdDTO.TenKH = rd.GetString(4);
                hdDTO.ThanhTien = rd.GetDouble(5);
                hdDTO.MaNV = rd.GetInt32(6);
                hdDTO.LoaiKH = rd.GetBoolean(7);
                hdDTO.TenNV = rd.GetString(8);
                lst.Add(hdDTO);
            }
            conn.Close();
            return lst;
        }
        //
        //Insert HoaDon
        //
        public int ThemHoaDon(HoaDonDTO hd)
        {
            OleDbConnection conn = DataProvider.ConnectDB();
            string sql = "Insert into HoaDon(NgayHD,SoHD,MaKH,TenKH,DiaChi,ThanhTien,MaNV,LoaiKH,GhiChu,TinhTrang) values(@NgayHD,@SoHD,@MaKH,@TenKH,@DiaChi,@ThanhTien,@MaNV,@LoaiKH,@GhiChu,@TinhTrang)";
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = conn;
            cmd.CommandText = sql;
            OleDbParameter pa;
            pa = new OleDbParameter("@NgayHD", OleDbType.Date);
            pa.Value = hd.NgayHD.ToShortDateString();
            cmd.Parameters.Add(pa);
            pa = new OleDbParameter("@SoHD", OleDbType.VarChar);
            pa.Value = "0";
            cmd.Parameters.Add(pa);
            pa = new OleDbParameter("@MaKH", OleDbType.Integer);
            pa.Value = hd.MaKH;
            cmd.Parameters.Add(pa);
            pa = new OleDbParameter("@TenKH", OleDbType.VarChar);
            pa.Value = hd.TenKH ;
            cmd.Parameters.Add(pa);
            pa = new OleDbParameter("@DiaChi", OleDbType.VarChar);
            pa.Value = hd.DiaChi;
            cmd.Parameters.Add(pa);
            pa = new OleDbParameter("@ThanhTien", OleDbType.Double);
            pa.Value = hd.ThanhTien;
            cmd.Parameters.Add(pa);
            pa = new OleDbParameter("@MaNV", OleDbType.Integer);
            pa.Value = hd.MaNV;
            cmd.Parameters.Add(pa);
            pa = new OleDbParameter("@LoaiKH", OleDbType.Boolean);
            pa.Value = hd.LoaiKH;
            cmd.Parameters.Add(pa);
            pa = new OleDbParameter("@GhiChu", OleDbType.VarChar);
            pa.Value = hd.GhiChu;
            cmd.Parameters.Add(pa);
            pa = new OleDbParameter("@TinhTrang", OleDbType.Boolean);
            pa.Value = hd.TinhTrang;
            cmd.Parameters.Add(pa);
            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            return kq;
        }
        public int LayMaHoaDonMoi()
        {
            string sql = "select MAX(MaHD) from HoaDon ";
            OleDbConnection conn = DataProvider.ConnectDB();

            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = conn;
            cmd.CommandText = sql;
            int kq = (int)cmd.ExecuteScalar();
            conn.Close();
            return kq;

        }
        public HoaDonDTO LayThongTinHoaDon(int MaHD)
        {
            string sql = "select * from HoaDon where MaHD=" + MaHD.ToString();
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = conn;
            cmd.CommandText = sql;
            OleDbDataReader rd= cmd.ExecuteReader();
            HoaDonDTO hd = new HoaDonDTO();
            if (rd.HasRows)
            {
                rd.Read();
                hd.MaHD = rd.GetInt32(0);
                hd.NgayHD = rd.GetDateTime(1);
                hd.SoHD = "0";
                hd.MaKH = rd.GetInt32(3);
                hd.TenKH = rd.GetString(4);
                hd.DiaChi = rd.GetString(5);
                hd.ThanhTien = rd.GetDouble(6);
                hd.MaNV = rd.GetInt32(7);
                hd.LoaiKH = rd.GetBoolean(8);
                hd.GhiChu = rd.GetString(9);
                hd.TinhTrang = rd.GetBoolean(10);
            }
            conn.Close();
            return hd;
           
        }
        //
        //Delete Hoa Don
        //
        public int XoaHoaDon(int MaHD)
        {
            string sql = "delete from HoaDon where MaHD=" + MaHD.ToString();
            OleDbConnection conn = DataProvider.ConnectDB();
            // conn.Open();
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = conn;
            cmd.CommandText = sql;

            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            return kq;
        }
        public int XoaHoaDonThanhTienbang0()
        {
            string sql = "delete from HoaDon where ThanhTien=0";
            OleDbConnection conn = DataProvider.ConnectDB();
            // conn.Open();
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = conn;
            cmd.CommandText = sql;

            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            return kq;
        }
        //
        //Update Hoa Don
        //
        public int Update(DTO.HoaDonDTO hd)
        {
            string sql = "update HoaDon set SoHD=@SoHD,MaKH=@MaKH,TenKH=@TenKH,DiaChi=@DiaChi,ThanhTien=@ThanhTien,MaNV=@MaNV,LoaiKH=yes,GhiChu=@GhiChu where MaHD=" + hd.MaHD.ToString();
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = conn;
            cmd.CommandText = sql;
            #region Tham Số
            OleDbParameter pa;
            pa = new OleDbParameter("@SoHD", OleDbType.VarChar);
            pa.Value = 0;
            cmd.Parameters.Add(pa);

            pa = new OleDbParameter("@MaKH", OleDbType.Integer);
            pa.Value = hd.MaKH;
            cmd.Parameters.Add(pa);

            pa = new OleDbParameter("@TenKH", OleDbType.VarChar);
            pa.Value = hd.TenKH;
            cmd.Parameters.Add(pa);

            pa = new OleDbParameter("@DiaChi", OleDbType.VarChar);
            pa.Value = hd.DiaChi;
            cmd.Parameters.Add(pa);

            pa = new OleDbParameter("@ThanhTien", OleDbType.Double);
            pa.Value = hd.ThanhTien;
            cmd.Parameters.Add(pa);

            pa = new OleDbParameter("@MaNV", OleDbType.Integer);
            pa.Value = hd.MaNV;
            cmd.Parameters.Add(pa);

            pa = new OleDbParameter("@GhiChu", OleDbType.VarChar);
            pa.Value = hd.GhiChu;
            cmd.Parameters.Add(pa);
            #endregion
            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            return kq;
        }
        public int UpdateTinhTrangHoaDon(int maHD,bool stt)
        {
            string sql = "update HoaDon set TinhTrang=@stt where MaHD=" + maHD.ToString();
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = conn;
            cmd.CommandText = sql;
            #region Tham Số
            OleDbParameter pa;
            pa = new OleDbParameter("@stt", OleDbType.Boolean);
            pa.Value = stt;
            cmd.Parameters.Add(pa);
            #endregion
            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            return kq;
        }
       
        //
        //Kiem Tra
        //
        public int IsExport(int MaHD)
        {
            string sql = "select TinhTrang from HoaDon where MaHD="+MaHD.ToString();
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = conn;
            cmd.CommandText = sql;
            bool kq=bool.Parse(cmd.ExecuteScalar().ToString());
            conn.Close();
            if ( kq== true)
                return 1;
            return 0;
            
        }
        public int IsMember(int MaHD)
        {
            string sql = "select LoaiKH from HoaDon where MaHD=" + MaHD.ToString();
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = conn;
            cmd.CommandText = sql;
            bool kq = bool.Parse(cmd.ExecuteScalar().ToString());
            conn.Close();
            if (kq == true)
                return 1;
            return 0;

        }
    }
}
